/**
 * @swagger
 * /api/high-goods/financial-analysis:
 *   get:
 *     tags:
 *       - 高价值品统计
 *     summary: 获取高价值品金融分析数据
 *     description: 分析高价值品的金额分布、成本效益和财务指标，支持时间筛选
 *     parameters:
 *       - in: query
 *         name: startTime
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始时间 (YYYY-MM-DD)
 *         example: "2023-01-01"
 *       - in: query
 *         name: endTime
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束时间 (YYYY-MM-DD)
 *         example: "2023-12-31"
 *     responses:
 *       200:
 *         description: 成功获取金融分析数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
                   type: integer
                   example: 200
                   description: 响应状态码
                 message:
                   type: string
                   example: "财务分析获取成功"
                   description: 响应消息
 *                 data:
                   type: object
                   properties:
                     amountDistribution:
                       type: object
                       properties:
                         title:
                           type: string
                           description: 标题
                         ranges:
                           type: array
                           items:
                             type: object
                             properties:
                               amountRange:
                                 type: string
                                 description: 金额范围
                               recordCount:
                                 type: number
                                 description: 记录数
                               totalAmountInRange:
                                 type: string
                                 description: 范围内总金额
                               totalQuantityInRange:
                                 type: number
                                 description: 范围内总数量
                               avgAmountInRange:
                                 type: string
                                 description: 范围内平均金额
                               avgQuantityInRange:
                                 type: string
                                 description: 范围内平均数量
                               percentage:
                                 type: string
                                 description: 占比
                     itemValueAnalysis:
                       type: object
                       properties:
                         title:
                           type: string
                           description: 标题
                         units:
                           type: array
                           items:
                             type: object
                             properties:
                               unitName:
                                 type: string
                                 description: 单位名称
                               totalAmount:
                                 type: string
                                 description: 总金额
                               totalQuantity:
                                 type: number
                                 description: 总数量
                               avgValuePerItem:
                                 type: string
                                 description: 单品平均价值
                               recordCount:
                                 type: number
                                 description: 记录数
                     # 移除预测性内容：异常分析
                     # 移除预测性内容：性价比分析
                     amountConcentrationAnalysis:
                       type: object
                       properties:
                         title:
                           type: string
                           description: 标题
                         statistics:
                           type: object
                           properties:
                             totalUnits:
                               type: number
                               description: 总单位数
                             totalAmount:
                               type: string
                               description: 总金额
                             concentrationRatios:
                               type: object
                               properties:
                                 top10Percent:
                                   type: string
                                   description: 前10%集中度
                                 top25Percent:
                                   type: string
                                   description: 前25%集中度
                                 top50Percent:
                                   type: string
                                   description: 前50%集中度
                             concentrationAmounts:
                               type: object
                               properties:
                                 top10PercentAmount:
                                   type: string
                                   description: 前10%金额
                                 top25PercentAmount:
                                   type: string
                                   description: 前25%金额
                                 top50PercentAmount:
                                   type: string
                                   description: 前50%金额
                     overallValueEfficiency:
                       type: object
                       properties:
                         title:
                           type: string
                           description: 标题
                         metrics:
                           type: object
                           properties:
                             avgAmountPerUnit:
                               type: string
                               description: 单位平均金额
                             avgQuantityPerUnit:
                               type: string
                               description: 单位平均数量
                             overallAvgValuePerItem:
                               type: string
                               description: 整体单品平均价值
                             totalUnits:
                               type: number
                               description: 总单位数
                             grandTotalAmount:
                               type: string
                               description: 总金额
                             grandTotalQuantity:
                               type: number
                               description: 总数量
                             # 移除预测性内容：价值集中度指数
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
const { success, error: createError, handleResponse } = require('../../../lib/response');
const { query } = require('../../../lib/db');

/**
 * 处理高价值品金融分析请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    // 获取时间筛选参数
    const { startTime, endTime } = req.query;
    
    // 构建时间筛选条件
    let timeCondition = '';
    if (startTime || endTime) {
      const conditions = [];
      if (startTime) conditions.push(`TJSJ >= '${startTime}'`);
      if (endTime) conditions.push(`TJSJ <= '${endTime}'`);
      timeCondition = ` WHERE ${conditions.join(' AND ')}`;
    }
    
    // 金额分布分析
    const amountDistributionQuery = `
      SELECT 
        CASE 
          WHEN COALESCE(GJZJE, 0) = 0 THEN '无金额'
          WHEN GJZJE <= 50000 THEN '0-5万'
          WHEN GJZJE <= 100000 THEN '5万-10万'
          WHEN GJZJE <= 500000 THEN '10万-50万'
          WHEN GJZJE <= 1000000 THEN '50万-100万'
          WHEN GJZJE <= 5000000 THEN '100万-500万'
          ELSE '500万以上'
        END as amountRange,
        COUNT(*) as recordCount,
        SUM(COALESCE(GJZJE, 0)) as totalAmountInRange,
        SUM(COALESCE(GJZSL, 0)) as totalQuantityInRange,
        AVG(COALESCE(GJZJE, 0)) as avgAmountInRange,
        AVG(COALESCE(GJZSL, 0)) as avgQuantityInRange
      FROM wd_zcgk_gjzzcdwfb${timeCondition}
      GROUP BY 
        CASE 
          WHEN COALESCE(GJZJE, 0) = 0 THEN '无金额'
          WHEN GJZJE <= 50000 THEN '0-5万'
          WHEN GJZJE <= 100000 THEN '5万-10万'
          WHEN GJZJE <= 500000 THEN '10万-50万'
          WHEN GJZJE <= 1000000 THEN '50万-100万'
          WHEN GJZJE <= 5000000 THEN '100万-500万'
          ELSE '500万以上'
        END
      ORDER BY 
        CASE 
          WHEN amountRange = '无金额' THEN 0
          WHEN amountRange = '0-5万' THEN 1
          WHEN amountRange = '5万-10万' THEN 2
          WHEN amountRange = '10万-50万' THEN 3
          WHEN amountRange = '50万-100万' THEN 4
          WHEN amountRange = '100万-500万' THEN 5
          ELSE 6
        END
    `;

    const amountDistributionResultResult = await query(amountDistributionQuery);

    // 单品价值分析
    const itemValueAnalysisQuery = `
      SELECT 
        COALESCE(DWMC, '未知单位') as unitName,
        SUM(COALESCE(GJZJE, 0)) as totalAmount,
        SUM(COALESCE(GJZSL, 0)) as totalQuantity,
        SUM(COALESCE(GJZJE, 0)) / NULLIF(SUM(COALESCE(GJZSL, 0)), 0) as avgValuePerItem,
        COUNT(*) as recordCount
      FROM wd_zcgk_gjzzcdwfb
      WHERE GJZSL > 0 AND GJZJE > 0${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
      GROUP BY DWMC
      HAVING totalQuantity > 0 AND totalAmount > 0
      ORDER BY avgValuePerItem DESC
      LIMIT 15
    `;

    const itemValueResultResult = await query(itemValueAnalysisQuery);

    // 移除预测性内容：金额异常分析

    // 移除预测性内容：单位性价比分析

    // 金额集中度分析
    const amountConcentrationQuery = `
      SELECT 
        COUNT(*) as totalUnits,
        SUM(unit_amount) as totalAmount,
        SUM(CASE WHEN rank_percent <= 10 THEN unit_amount ELSE 0 END) as top10PercentAmount,
        SUM(CASE WHEN rank_percent <= 25 THEN unit_amount ELSE 0 END) as top25PercentAmount,
        SUM(CASE WHEN rank_percent <= 50 THEN unit_amount ELSE 0 END) as top50PercentAmount
      FROM (
        SELECT 
          DWMC,
          SUM(COALESCE(GJZJE, 0)) as unit_amount,
          ROW_NUMBER() OVER (ORDER BY SUM(COALESCE(GJZJE, 0)) DESC) * 100.0 / COUNT(*) OVER() as rank_percent
        FROM wd_zcgk_gjzzcdwfb
        WHERE DWMC IS NOT NULL AND DWMC != ''${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
        GROUP BY DWMC
        HAVING unit_amount > 0
      ) ranked_units
    `;

    const concentrationResultResult = await query(amountConcentrationQuery);
    const concentrationData = concentrationResultResult[0];

    // 价值效率指标
    const valueEfficiencyQuery = `
      SELECT 
        SUM(COALESCE(GJZJE, 0)) / NULLIF(COUNT(DISTINCT DWMC), 0) as avgAmountPerUnit,
        SUM(COALESCE(GJZSL, 0)) / NULLIF(COUNT(DISTINCT DWMC), 0) as avgQuantityPerUnit,
        SUM(COALESCE(GJZJE, 0)) / NULLIF(SUM(COALESCE(GJZSL, 0)), 0) as overallAvgValuePerItem,
        COUNT(DISTINCT DWMC) as totalUnits,
        SUM(COALESCE(GJZJE, 0)) as grandTotalAmount,
        SUM(COALESCE(GJZSL, 0)) as grandTotalQuantity
      FROM wd_zcgk_gjzzcdwfb
      WHERE DWMC IS NOT NULL AND DWMC != ''${timeCondition ? timeCondition.replace('WHERE', 'AND') : ''}
    `;

    const efficiencyResultResult = await query(valueEfficiencyQuery);
    const efficiencyData = efficiencyResultResult[0];

    const responseData = {
      amountDistribution: {
        title: "高价值品金额分布",
        ranges: amountDistributionResultResult.map(row => ({
          amountRange: row.amountRange,
          recordCount: parseInt(row.recordCount),
          totalAmountInRange: parseFloat(row.totalAmountInRange || 0).toFixed(2),
          totalQuantityInRange: parseInt(row.totalQuantityInRange || 0),
          avgAmountInRange: parseFloat(row.avgAmountInRange || 0).toFixed(2),
          avgQuantityInRange: parseFloat(row.avgQuantityInRange || 0).toFixed(2),
          percentage: ((parseInt(row.recordCount) / amountDistributionResultResult.reduce((sum, r) => sum + parseInt(r.recordCount), 0)) * 100).toFixed(2)
        }))
      },
      itemValueAnalysis: {
        title: "单品价值分析(Top15)",
        units: itemValueResultResult.map(row => ({
          unitName: row.unitName,
          totalAmount: parseFloat(row.totalAmount || 0).toFixed(2),
          totalQuantity: parseInt(row.totalQuantity || 0),
          avgValuePerItem: parseFloat(row.avgValuePerItem || 0).toFixed(2),
          recordCount: parseInt(row.recordCount)
        }))
      },
      // 移除预测性内容：异常分析和性价比分析
      amountConcentrationAnalysis: {
        title: "金额集中度分析",
        statistics: {
          totalUnits: parseInt(concentrationData.totalUnits || 0),
          totalAmount: parseFloat(concentrationData.totalAmount || 0).toFixed(2),
          concentrationRatios: {
            top10Percent: ((parseFloat(concentrationData.top10PercentAmount || 0) / parseFloat(concentrationData.totalAmount || 1)) * 100).toFixed(2),
            top25Percent: ((parseFloat(concentrationData.top25PercentAmount || 0) / parseFloat(concentrationData.totalAmount || 1)) * 100).toFixed(2),
            top50Percent: ((parseFloat(concentrationData.top50PercentAmount || 0) / parseFloat(concentrationData.totalAmount || 1)) * 100).toFixed(2)
          },
          concentrationAmounts: {
            top10PercentAmount: parseFloat(concentrationData.top10PercentAmount || 0).toFixed(2),
            top25PercentAmount: parseFloat(concentrationData.top25PercentAmount || 0).toFixed(2),
            top50PercentAmount: parseFloat(concentrationData.top50PercentAmount || 0).toFixed(2)
          }
        }
      },
      overallValueEfficiency: {
        title: "整体价值效率指标",
        metrics: {
          avgAmountPerUnit: parseFloat(efficiencyData.avgAmountPerUnit || 0).toFixed(2),
          avgQuantityPerUnit: parseFloat(efficiencyData.avgQuantityPerUnit || 0).toFixed(2),
          overallAvgValuePerItem: parseFloat(efficiencyData.overallAvgValuePerItem || 0).toFixed(2),
          totalUnits: parseInt(efficiencyData.totalUnits || 0),
          grandTotalAmount: parseFloat(efficiencyData.grandTotalAmount || 0).toFixed(2),
          grandTotalQuantity: parseInt(efficiencyData.grandTotalQuantity || 0),
          // 移除预测性内容：价值集中度指数
        }
      }
    };

    return handleResponse(res, success(responseData, "金额分析获取成功"));
    
  } catch (err) {
    console.error(arguments[1], err);
    return handleResponse(res, createError("获取金额分析失败", 500));
  }
}

export default withCors(handler, ['GET']);